package com.infodms.dms.dao.claim.application;

import java.math.BigDecimal;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.StringTokenizer;

import com.infodms.dms.bean.ClaimOrderBean;
import com.infodms.dms.bean.auditBean;
import com.infodms.dms.common.Constant;
import com.infodms.dms.common.Utility;
import com.infodms.dms.dao.common.BaseDao;
import com.infodms.dms.po.TcUserPO;
import com.infodms.dms.po.TtAsActivityPO;
import com.infodms.dms.po.TtAsWrApplicationExtPO;
import com.infodms.dms.po.TtAsWrApplicationPO;
import com.infodms.dms.po.TtAsWrClaimBalancePO;
import com.infodms.dms.po.TtAsWrLabouritemPO;
import com.infodms.dms.po.TtAsWrNetitemPO;
import com.infodms.dms.po.TtAsWrPartsitemPO;
import com.infodms.dms.util.CommonUtils;
import com.infodms.dms.util.StringUtil;
import com.infodms.dms.util.UserProvinceRelation;
import com.infodms.dms.util.businessUtil.GetVinUtil;
import com.infoservice.po3.bean.PO;
import com.infoservice.po3.bean.PageResult;

/**
 * 索赔申请单审核
 * 主要功能：
 *    1、要审核的索赔申请单查询
 *    2、索赔申请单审核
 * @author XZM
 *
 */
@SuppressWarnings("unchecked")
public class ClaimManualAuditingDao extends BaseDao {
	
	@Override
	protected PO wrapperPO(ResultSet rs, int idx) {
		return null;
	}

	/**
	 * 查询索赔申请单信息
	 * 注意：现在语句只支持二级经销商,需要支持多级以上则需要调整
	 * @param crBean 查询条件
	 * @param curPage 当前页
	 * @param pageSize 每页记录数
	 * @return PageResult<Map<String,Object>> 索赔申请单信息
	 */
	public PageResult<Map<String,Object>> queryClaimAudit(ClaimOrderBean orderBean,Integer curPage,Integer pageSize){
		
		StringBuilder sBuilder = new StringBuilder();
		List<Object> paramList = new ArrayList<Object>();
		
		//paramList.add(orderBean.getCompanyId());
		paramList.add(orderBean.getOrgId());
	    //paramList.add(orderBean.getAreaIds());
		
		//初始化索赔申请单查询条件
		this.createWhereMap("YIELDLY" ,"IN" ,orderBean.getYieldlys() , sBuilder, paramList,2,"","A");
		this.createWhereMap("YIELDLY" ,"=" ,orderBean.getYieldly() , sBuilder, paramList,2,"","A");
		this.createWhereMap("CLAIM_NO" ,"LIKE" ,orderBean.getClaimNo() , sBuilder, paramList,2,"","A");
		this.createWhereMap("RO_NO" ,"LIKE" ,orderBean.getRoNo() , sBuilder, paramList,2,"","A");
		this.createWhereMap("LINE_NO","=" ,orderBean.getLineNo(), sBuilder, paramList,2,"","A");
		this.createWhereMap("CLAIM_TYPE","=" ,orderBean.getClaimType() , sBuilder, paramList,2,"","A");
		this.createWhereMap("AUTH_CODE" ,"=" ,orderBean.getAuthCode(), sBuilder, paramList,2,"","A");
		this.createWhereMap("OEM_COMPANY_ID","=" ,orderBean.getCompanyId().toString() , sBuilder, paramList,2,"","A");
		this.createWhereMap("PROVINCE_ID" ,"=" ,orderBean.getProvinceCode(), sBuilder, paramList,2,"","B");
		this.createWhereMap("DEALER_CODE" ,"IN" ,orderBean.getDealerCodes(), sBuilder, paramList,2,"","B");
		this.createWhereMap("DEALER_SHORTNAME" ,"LIKE" ,orderBean.getDealerName(), sBuilder, paramList,2,"","B");
		this.createWhereMap("STATUS", "=" ,orderBean.getClaimStatus() , sBuilder, paramList,2,"","A");
		this.createWhereMap("CREATE_DATE", ">=" ,orderBean.getApplyStartDate(), sBuilder, paramList,1,"YYYY-MM-DD HH24:MI:SS","A");
		this.createWhereMap("CREATE_DATE","<=" ,orderBean.getApplyEndDate() , sBuilder, paramList,1,"YYYY-MM-DD HH24:MI:SS","A");
		
		String whereMap = sBuilder.toString();
		
		PageResult<Map<String, Object>> result = null;
		String sql = "";
		StringBuilder sqlBulider = new StringBuilder();
		sqlBulider.append(" SELECT /*+ INDEX (A IDX_TT_APPLICATION_STATUS) */ A.CLAIM_NO, B.DEALER_CODE,B.DEALER_SHORTNAME DEALER_NAME,\n" );
		sqlBulider.append("      A.RO_NO AS RO_NO,A.LINE_NO,A.CLAIM_TYPE,A.VIN,\n" );
		sqlBulider.append("      TO_CHAR(A.CREATE_DATE, 'YYYY-MM-DD') CREATE_DATE,A.STATUS,\n" );
		sqlBulider.append("      A.ID,A.REPAIR_TOTAL\n" );
		sqlBulider.append(" FROM TT_AS_WR_APPLICATION A,\n" );
		sqlBulider.append("      TM_DEALER B,\n" );
		//sqlBulider.append("      TC_USER_REGION_RELATION J,\n" );
		sqlBulider.append("            vw_org_dealer_service vw, \n" );
		sqlBulider.append("      (SELECT ORG_ID\n" );
		sqlBulider.append("                 FROM TM_ORG ORG\n" );
		sqlBulider.append("                WHERE ORG.STATUS = "+Constant.STATUS_ENABLE+"\n" );
		sqlBulider.append("                START WITH ORG.ORG_ID = ?\n" );
		sqlBulider.append("               CONNECT BY PRIOR ORG.ORG_ID = ORG.PARENT_ORG_ID) ORG\n" );
		sqlBulider.append("WHERE 1 = 1\n" );
		sqlBulider.append("  AND A.DEALER_ID = B.DEALER_ID\n" );
		//sqlBulider.append("  AND B.PROVINCE_ID = J.REGION_CODE\n");
		sqlBulider.append("  and a.dealer_id = vw.dealer_id AND ORG.ORG_ID = vw.root_org_id\n" );
		sqlBulider.append("  AND EXISTS (SELECT 1\n" );
		sqlBulider.append("       FROM TM_DEALER_BUSINESS_AREA TDBA\n" );
		sqlBulider.append("      WHERE TDBA.DEALER_ID = A.DEALER_ID\n" );
		sqlBulider.append("        AND TDBA.AREA_ID IN ("+orderBean.getAreaIds()+")\n)");
		sqlBulider.append(whereMap + "\n");
		sqlBulider.append(GetVinUtil.getVins(orderBean.getVin(), "A") + "\n");
		//sqlBulider.append(UserProvinceRelation.getDealerIds(Long.parseLong(orderBean.getUserId()), "b")) ;
		//sqlBulider.append(" AND J.USER_ID = ").append(orderBean.getUserId());
		sqlBulider.append("ORDER BY A.ID DESC");
		
		sql = sqlBulider.toString();
		
		result = (PageResult<Map<String, Object>>)this.pageQuery(sql,
																paramList,
																this.getClass().getName()+".queryClaim()",
																pageSize,
																curPage);
		return result;
	}
	
	
	public PageResult<Map<String,Object>> queryClaimAuditDelete(ClaimOrderBean orderBean,Integer curPage,Integer pageSize){
		StringBuilder sBuilder = new StringBuilder();
		List<Object> paramList = new ArrayList<Object>();
		
		paramList.add(orderBean.getOrgId());
		
		//初始化索赔申请单查询条件
		this.createWhereMap("YIELDLY" ,"IN" ,orderBean.getYieldlys() , sBuilder, paramList,2,"","A");
		this.createWhereMap("YIELDLY" ,"=" ,orderBean.getYieldly() , sBuilder, paramList,2,"","A");
		this.createWhereMap("CLAIM_NO" ,"LIKE" ,orderBean.getClaimNo() , sBuilder, paramList,2,"","A");
		this.createWhereMap("RO_NO" ,"LIKE" ,orderBean.getRoNo() , sBuilder, paramList,2,"","A");
		this.createWhereMap("LINE_NO","=" ,orderBean.getLineNo(), sBuilder, paramList,2,"","A");
		this.createWhereMap("CLAIM_TYPE","=" ,orderBean.getClaimType() , sBuilder, paramList,2,"","A");
		this.createWhereMap("OEM_COMPANY_ID","=" ,orderBean.getCompanyId().toString() , sBuilder, paramList,2,"","A");
		this.createWhereMap("PROVINCE_ID" ,"=" ,orderBean.getProvinceCode(), sBuilder, paramList,2,"","B");
		this.createWhereMap("DEALER_CODE" ,"IN" ,orderBean.getDealerCodes(), sBuilder, paramList,2,"","B");
		this.createWhereMap("DEALER_SHORTNAME" ,"LIKE" ,orderBean.getDealerName(), sBuilder, paramList,2,"","B");
		this.createWhereMap("CREATE_DATE", ">=" ,orderBean.getApplyStartDate(), sBuilder, paramList,1,"YYYY-MM-DD HH24:MI:SS","A");
		this.createWhereMap("CREATE_DATE","<=" ,orderBean.getApplyEndDate() , sBuilder, paramList,1,"YYYY-MM-DD HH24:MI:SS","A");
		
		String whereMap = sBuilder.toString();
		
		PageResult<Map<String, Object>> result = null;
		String sql = "";
		StringBuilder sqlBulider = new StringBuilder();
		sqlBulider.append(" SELECT /*+ INDEX (A IDX_TT_APPLICATION_STATUS) */ A.CLAIM_NO, B.DEALER_CODE,B.DEALER_SHORTNAME DEALER_NAME,\n" );
		sqlBulider.append("      A.RO_NO AS RO_NO,A.LINE_NO,A.CLAIM_TYPE,A.VIN,B.PHONE,\n" );
		sqlBulider.append("      TO_CHAR(A.CREATE_DATE, 'YYYY-MM-DD') CREATE_DATE,A.STATUS,\n" );
		sqlBulider.append("      A.ID,A.REPAIR_TOTAL\n" );
		sqlBulider.append(" FROM tt_as_wr_application_backup A,\n" );
		sqlBulider.append("      TM_DEALER B,\n" );
		sqlBulider.append("      TM_DEALER_ORG_RELATION REL,\n" );
		sqlBulider.append("      (SELECT ORG_ID\n" );
		sqlBulider.append("                 FROM TM_ORG ORG\n" );
		sqlBulider.append("                WHERE ORG.STATUS = "+Constant.STATUS_ENABLE+"\n" );
		sqlBulider.append("                START WITH ORG.ORG_ID = ?\n" );
		sqlBulider.append("               CONNECT BY PRIOR ORG.ORG_ID = ORG.PARENT_ORG_ID) ORG\n" );
		sqlBulider.append("WHERE 1 = 1 and A.status in('"+Constant.CLAIM_APPLY_ORD_TYPE_04+"','"+Constant.CLAIM_APPLY_ORD_TYPE_05+"','"+Constant.CLAIM_APPLY_ORD_TYPE_06+"')\n" );
		sqlBulider.append("  AND A.DEALER_ID = B.DEALER_ID\n" );
		sqlBulider.append("  AND (REL.DEALER_ID = B.DEALER_ID OR REL.DEALER_ID = B.PARENT_DEALER_D)\n" );
		sqlBulider.append("  AND ORG.ORG_ID=REL.ORG_ID\n" );
		sqlBulider.append("  AND EXISTS (SELECT 1\n" );
		sqlBulider.append("       FROM TM_DEALER_BUSINESS_AREA TDBA\n" );
		sqlBulider.append("      WHERE TDBA.DEALER_ID = A.DEALER_ID\n" );
		sqlBulider.append("        AND TDBA.AREA_ID IN ("+orderBean.getAreaIds()+")\n)");
		sqlBulider.append(whereMap + "\n");
		sqlBulider.append(GetVinUtil.getVins(orderBean.getVin(), "A") + "\n");
		sqlBulider.append("ORDER BY A.ID DESC");
		
		sql = sqlBulider.toString();
		System.out.println("sql=="+sql);
		result = (PageResult<Map<String, Object>>)this.pageQuery(sql,
																paramList,
																this.getClass().getName()+".queryClaim()",
																pageSize,
																curPage);
		return result;
	}
	
	public PageResult<Map<String,Object>> queryClaimAuditDelete1(ClaimOrderBean orderBean,Integer curPage,Integer pageSize){
		StringBuilder sBuilder = new StringBuilder();
		List<Object> paramList = new ArrayList<Object>();
		
		paramList.add(orderBean.getOrgId());
		
		//初始化索赔申请单查询条件
		this.createWhereMap("YIELDLY" ,"IN" ,orderBean.getYieldlys() , sBuilder, paramList,2,"","A");
		this.createWhereMap("YIELDLY" ,"=" ,orderBean.getYieldly() , sBuilder, paramList,2,"","A");
		this.createWhereMap("CLAIM_NO" ,"LIKE" ,orderBean.getClaimNo() , sBuilder, paramList,2,"","A");
		this.createWhereMap("RO_NO" ,"LIKE" ,orderBean.getRoNo() , sBuilder, paramList,2,"","A");
		this.createWhereMap("LINE_NO","=" ,orderBean.getLineNo(), sBuilder, paramList,2,"","A");
		this.createWhereMap("CLAIM_TYPE","=" ,orderBean.getClaimType() , sBuilder, paramList,2,"","A");
		this.createWhereMap("OEM_COMPANY_ID","=" ,orderBean.getCompanyId().toString() , sBuilder, paramList,2,"","A");
		this.createWhereMap("PROVINCE_ID" ,"=" ,orderBean.getProvinceCode(), sBuilder, paramList,2,"","B");
		this.createWhereMap("DEALER_CODE" ,"IN" ,orderBean.getDealerCodes(), sBuilder, paramList,2,"","B");
		this.createWhereMap("DEALER_SHORTNAME" ,"LIKE" ,orderBean.getDealerName(), sBuilder, paramList,2,"","B");
		this.createWhereMap("CREATE_DATE", ">=" ,orderBean.getApplyStartDate(), sBuilder, paramList,1,"YYYY-MM-DD HH24:MI:SS","A");
		this.createWhereMap("CREATE_DATE","<=" ,orderBean.getApplyEndDate() , sBuilder, paramList,1,"YYYY-MM-DD HH24:MI:SS","A");
		
		String whereMap = sBuilder.toString();
		
		PageResult<Map<String, Object>> result = null;
		String sql = "";
		StringBuilder sqlBulider = new StringBuilder();
		sqlBulider.append(" SELECT /*+ INDEX (A IDX_TT_APPLICATION_STATUS) */ A.CLAIM_NO, B.DEALER_CODE,B.DEALER_SHORTNAME DEALER_NAME,\n" );
		sqlBulider.append("      A.RO_NO AS RO_NO,A.LINE_NO,A.CLAIM_TYPE,A.VIN,B.PHONE,\n" );
		sqlBulider.append("      TO_CHAR(A.CREATE_DATE, 'YYYY-MM-DD') CREATE_DATE,A.STATUS,\n" );
		sqlBulider.append("      A.ID,A.REPAIR_TOTAL\n" );
		sqlBulider.append(" FROM TT_AS_WR_APPLICATION A,\n" );
		sqlBulider.append("      TM_DEALER B,\n" );
		sqlBulider.append("      TC_USER_REGION_RELATION J,\n" );
		sqlBulider.append("      TM_DEALER_ORG_RELATION REL,\n" );
		sqlBulider.append("      (SELECT ORG_ID\n" );
		sqlBulider.append("                 FROM TM_ORG ORG\n" );
		sqlBulider.append("                WHERE ORG.STATUS = "+Constant.STATUS_ENABLE+"\n" );
		sqlBulider.append("                START WITH ORG.ORG_ID = ?\n" );
		sqlBulider.append("               CONNECT BY PRIOR ORG.ORG_ID = ORG.PARENT_ORG_ID) ORG\n" );
		sqlBulider.append("WHERE 1 = 1 and a.application_del='"+Constant.RO_APP_STATUS_03.toString()+"'\n" );
		sqlBulider.append("  AND A.DEALER_ID = B.DEALER_ID\n" );
		sqlBulider.append("  AND B.PROVINCE_ID = J.REGION_CODE\n");
		sqlBulider.append("  AND (REL.DEALER_ID = B.DEALER_ID OR REL.DEALER_ID = B.PARENT_DEALER_D)\n" );
		sqlBulider.append("  AND ORG.ORG_ID=REL.ORG_ID\n" );
		sqlBulider.append("  AND EXISTS (SELECT 1\n" );
		sqlBulider.append("       FROM TM_DEALER_BUSINESS_AREA TDBA\n" );
		sqlBulider.append("      WHERE TDBA.DEALER_ID = A.DEALER_ID\n" );
		sqlBulider.append("        AND TDBA.AREA_ID IN ("+orderBean.getAreaIds()+")\n)");
		sqlBulider.append(whereMap + "\n");
		sqlBulider.append(GetVinUtil.getVins(orderBean.getVin(), "A") + "\n");
		sqlBulider.append(" AND J.USER_ID = ").append(orderBean.getUserId());
		sqlBulider.append("ORDER BY A.ID DESC");
		sql = sqlBulider.toString();
		System.out.println("sql==="+sql);
		result = (PageResult<Map<String, Object>>)this.pageQuery(sql,
																paramList,
																this.getClass().getName()+".queryClaim()",
																pageSize,
																curPage);
		return result;
	}
	
	
	
	public PageResult<Map<String,Object>> queryClaimAudit1(ClaimOrderBean orderBean,Integer curPage,Integer pageSize){
		
		StringBuilder sBuilder = new StringBuilder();
		List<Object> paramList = new ArrayList<Object>();
		
		//paramList.add(orderBean.getCompanyId());
		paramList.add(orderBean.getOrgId());
	    //paramList.add(orderBean.getAreaIds());
		
		//初始化索赔申请单查询条件
		this.createWhereMap("YIELDLY" ,"IN" ,orderBean.getYieldlys() , sBuilder, paramList,2,"","A");
		this.createWhereMap("YIELDLY" ,"=" ,orderBean.getYieldly() , sBuilder, paramList,2,"","A");
		this.createWhereMap("CLAIM_NO" ,"LIKE" ,orderBean.getClaimNo() , sBuilder, paramList,2,"","A");
		this.createWhereMap("RO_NO" ,"LIKE" ,orderBean.getRoNo() , sBuilder, paramList,2,"","A");
		this.createWhereMap("LINE_NO","=" ,orderBean.getLineNo(), sBuilder, paramList,2,"","A");
		this.createWhereMap("CLAIM_TYPE","=" ,orderBean.getClaimType() , sBuilder, paramList,2,"","A");
		this.createWhereMap("AUTH_CODE" ,"=" ,orderBean.getAuthCode(), sBuilder, paramList,2,"","A");
		this.createWhereMap("OEM_COMPANY_ID","=" ,orderBean.getCompanyId().toString() , sBuilder, paramList,2,"","A");
		this.createWhereMap("PROVINCE_ID" ,"=" ,orderBean.getProvinceCode(), sBuilder, paramList,2,"","B");
		this.createWhereMap("DEALER_CODE" ,"IN" ,orderBean.getDealerCodes(), sBuilder, paramList,2,"","B");
		this.createWhereMap("DEALER_SHORTNAME" ,"LIKE" ,orderBean.getDealerName(), sBuilder, paramList,2,"","B");
		this.createWhereMap("STATUS", "=" ,orderBean.getClaimStatus() , sBuilder, paramList,2,"","A");
		this.createWhereMap("CREATE_DATE", ">=" ,orderBean.getApplyStartDate(), sBuilder, paramList,1,"YYYY-MM-DD HH24:MI:SS","A");
		this.createWhereMap("CREATE_DATE","<=" ,orderBean.getApplyEndDate() , sBuilder, paramList,1,"YYYY-MM-DD HH24:MI:SS","A");
		
		String whereMap = sBuilder.toString();
		
		PageResult<Map<String, Object>> result = null;
		String sql = "";
		StringBuilder sqlBulider = new StringBuilder();
		sqlBulider.append(" SELECT /*+ INDEX (A IDX_TT_APPLICATION_STATUS) */ A.CLAIM_NO, B.DEALER_CODE,B.DEALER_SHORTNAME DEALER_NAME,\n" );
		sqlBulider.append("      A.RO_NO AS RO_NO,A.LINE_NO,A.CLAIM_TYPE,A.VIN,\n" );
		sqlBulider.append("      TO_CHAR(A.CREATE_DATE, 'YYYY-MM-DD') CREATE_DATE,A.STATUS,\n" );
		sqlBulider.append("      A.ID,A.REPAIR_TOTAL\n" );
		sqlBulider.append(" FROM TT_AS_WR_APPLICATION A,\n" );
		sqlBulider.append("      TM_DEALER B,\n" );
		sqlBulider.append("      vw_org_dealer_service s,\n");
    	sqlBulider.append("      TC_USER_REGION_RELATION J,\n" );
//		sqlBulider.append("      TM_DEALER_ORG_RELATION REL,\n" );
		sqlBulider.append("      (SELECT ORG_ID\n" );
		sqlBulider.append("                 FROM TM_ORG ORG\n" );
		sqlBulider.append("                WHERE ORG.STATUS = "+Constant.STATUS_ENABLE+"\n" );
		sqlBulider.append("                START WITH ORG.ORG_ID = ?\n" );
		sqlBulider.append("               CONNECT BY PRIOR ORG.ORG_ID = ORG.PARENT_ORG_ID) ORG\n" );
		sqlBulider.append("WHERE 1 = 1\n" );
		sqlBulider.append("  AND A.DEALER_ID = B.DEALER_ID\n" );
		sqlBulider.append("  AND B.PROVINCE_ID = J.REGION_CODE\n");
		sqlBulider.append("  and s.dealer_id = b.dealer_id\n");
		sqlBulider.append("  and org.org_id = s.root_org_id\n");
//		sqlBulider.append("  AND (REL.DEALER_ID = B.DEALER_ID OR REL.DEALER_ID = B.PARENT_DEALER_D)\n" );
//		sqlBulider.append("  AND ORG.ORG_ID=REL.ORG_ID\n" );
		sqlBulider.append("  AND EXISTS (SELECT 1\n" );
		sqlBulider.append("       FROM TM_DEALER_BUSINESS_AREA TDBA\n" );
		sqlBulider.append("      WHERE TDBA.DEALER_ID = A.DEALER_ID\n" );
		sqlBulider.append("        AND TDBA.AREA_ID IN ("+orderBean.getAreaIds()+")\n)");
		sqlBulider.append(whereMap + "\n");
		sqlBulider.append(GetVinUtil.getVins(orderBean.getVin(), "A") + "\n");
		sqlBulider.append(" AND J.USER_ID = ").append(orderBean.getUserId());
		//sqlBulider.append(UserProvinceRelation.getDealerIds(Long.parseLong(orderBean.getUserId()), "b")) ;
		sqlBulider.append(" ORDER BY A.ID DESC");
		
		sql = sqlBulider.toString();
		
		result = (PageResult<Map<String, Object>>)this.pageQuery(sql,
																paramList,
																this.getClass().getName()+".queryClaim()",
																pageSize,
																curPage);
		return result;
	}
	
	
	public Integer getUserRegionCount(Long userId){
		Integer i = 0;
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(*) COUNT FROM TC_USER_REGION_RELATION WHERE USER_ID = ").append(userId);

		Map<String, Object> map = pageQueryMap(sql.toString(), null, getFunName());
		i = Integer.parseInt(String.valueOf(map.get("COUNT")));
		return i;
	}
	
	/**
	 * 查询索赔申请单信息
	 * 注意：现在语句只支持二级经销商,需要支持多级以上则需要调整
	 * @param crBean 查询条件
	 * @param curPage 当前页
	 * @param pageSize 每页记录数
	 * @return PageResult<Map<String,Object>> 索赔申请单信息
	 */
	public PageResult<Map<String,Object>> queryClaim(ClaimOrderBean orderBean,Integer curPage,Integer pageSize){
		
		StringBuilder sBuilder1 = new StringBuilder();
		StringBuilder sBuilder = new StringBuilder();
		List<Object> paramList = new ArrayList<Object>();
		
		//初始化索赔申请单查询条件
		this.createWhereMap("YIELDLY" ,"IN" ,orderBean.getYieldlys() , sBuilder1, paramList,2,"","A1");
		this.createWhereMap("YIELDLY" ,"=" ,orderBean.getYieldly() , sBuilder1, paramList,2,"","A1");
		this.createWhereMap("STATUS" ,"=" ,orderBean.getStatus() , sBuilder1, paramList,2,"","A1");
		this.createWhereMap("CLAIM_NO" ,"LIKE" ,orderBean.getClaimNo() , sBuilder1, paramList,2,"","A1");
		this.createWhereMap("RO_NO" ,"LIKE" ,orderBean.getRoNo() , sBuilder1, paramList,2,"","A1");
		this.createWhereMap("LINE_NO","=" ,orderBean.getLineNo(), sBuilder1, paramList,2,"","A1");
		this.createWhereMap("CLAIM_TYPE","=" ,orderBean.getClaimType() , sBuilder1, paramList,2,"","A1");
		this.createWhereMap("AUTH_CODE" ,"=" ,orderBean.getAuthCode(), sBuilder1, paramList,2,"","A1");
		this.createWhereMap("OEM_COMPANY_ID","=" ,orderBean.getCompanyId().toString() , sBuilder1, paramList,2,"","A1");
		this.createWhereMap("CREATE_DATE", ">=" ,orderBean.getApplyStartDate(), sBuilder1, paramList,1,"YYYY-MM-DD HH24:MI:SS","A1");
		this.createWhereMap("CREATE_DATE","<=" ,orderBean.getApplyEndDate() , sBuilder1, paramList,1,"YYYY-MM-DD HH24:MI:SS","A1");
		this.createWhereMap("DEALER_CODE" ,"IN" ,orderBean.getDealerCodes(), sBuilder, paramList,2,"","B");
		this.createWhereMap("DEALER_SHORTNAME" ,"LIKE" ,orderBean.getDealerName(), sBuilder, paramList,2,"","B");
		
		PageResult<Map<String, Object>> result = null;
		String sql = "";
		StringBuilder sqlBulider= new StringBuilder();
		sqlBulider.append("SELECT /*+ INDEX (A IDX_TT_APPLICATION_STATUS) */\n" );
		sqlBulider.append(" A.AUDITING_DATE, A.CLAIM_NO, B.DEALER_CODE, B.DEALER_SHORTNAME DEALER_NAME, A.RO_NO AS RO_NO, A.LINE_NO, A.CLAIM_TYPE,\n" );
		sqlBulider.append(" A.VIN, TO_CHAR(A.CREATE_DATE, 'YYYY-MM-DD') CREATE_DATE, A.STATUS, A.ID, A.REPAIR_TOTAL,\n" );
		sqlBulider.append(" (SELECT U.NAME\n" );
		sqlBulider.append("   FROM TC_USER U\n" );
		sqlBulider.append("   WHERE U.USER_ID = (SELECT W.CREATE_BY\n" );
		sqlBulider.append("                      FROM TT_AS_WR_APPAUTHITEM W\n" );
		sqlBulider.append("                      WHERE W.CREATE_DATE = (SELECT MAX(WA.CREATE_DATE) FROM TT_AS_WR_APPAUTHITEM WA WHERE WA.ID = A.ID) AND\n" );
		sqlBulider.append("                            W.ID = A.ID AND ROWNUM = 1)) AS AUTH_PERSON\n" );
		sqlBulider.append("FROM (SELECT *\n" );
		sqlBulider.append("       FROM TT_AS_WR_APPLICATION A1\n" );
		sqlBulider.append("       WHERE EXISTS (SELECT 1\n" );
		sqlBulider.append("              FROM TM_DEALER_BUSINESS_AREA TDBA\n" );
		sqlBulider.append("              WHERE TDBA.DEALER_ID = A1.DEALER_ID AND\n" );
		sqlBulider.append("                    TDBA.AREA_ID IN ("+orderBean.getAreaIds()+"))\n" );
		sqlBulider.append("       AND A1.STATUS IN ("+Constant.CLAIM_APPLY_ORD_TYPE_03+","+Constant.CLAIM_APPLY_ORD_TYPE_04+")\n");
		sqlBulider.append("       "+GetVinUtil.getVins(orderBean.getVin(), "A1") + "\n");
		if(StringUtil.notNull(orderBean.getApproveDate())){
			sqlBulider.append("       AND A1.auditing_date >= to_date('").append(orderBean.getApproveDate());
			sqlBulider.append(" 00:00:00','yyyy-MM-dd hh24:mi:ss')\n");
		}
		if(StringUtil.notNull(orderBean.getApproveDate2())){
			sqlBulider.append("       AND A1.auditing_date <= to_date('").append(orderBean.getApproveDate2());
			sqlBulider.append(" 23:59:59','yyyy-MM-dd hh24:mi:ss')\n");
		}
		if(StringUtil.notNull(orderBean.getApproveName())){
			sqlBulider.append("       AND A1.ID in\n");
			sqlBulider.append("      (select id\n");  
			sqlBulider.append("         from tt_as_wr_appauthitem wa\n");  
			sqlBulider.append("        where wa.create_by in\n");  
			sqlBulider.append("              (select u.user_id from tc_user u where u.name like '%"+orderBean.getApproveName()+"%'))\n");
		}
		sqlBulider.append("             "+sBuilder1.toString()+") A\n" );
		sqlBulider.append("             , TM_DEALER B\n" );
		sqlBulider.append("             , TM_DEALER_ORG_RELATION REL,\n" );
		sqlBulider.append("     (SELECT ORG_ID\n" );
		sqlBulider.append("       FROM TM_ORG ORG\n" );
		sqlBulider.append("       WHERE ORG.STATUS = "+Constant.STATUS_ENABLE+"\n" );
		sqlBulider.append("       START WITH ORG.ORG_ID = "+orderBean.getOrgId()+"\n" );
		sqlBulider.append("       CONNECT BY PRIOR ORG.ORG_ID = ORG.PARENT_ORG_ID) ORG\n" );
		sqlBulider.append("WHERE 1 = 1\n" );
		sqlBulider.append("AND A.DEALER_ID = B.DEALER_ID\n" );
		sqlBulider.append("AND (REL.DEALER_ID = B.DEALER_ID OR REL.DEALER_ID = B.PARENT_DEALER_D)\n" );
		sqlBulider.append("AND ORG.ORG_ID = REL.ORG_ID\n");
		sqlBulider.append(""+sBuilder.toString()+"");
		//sqlBulider.append("ORDER BY A.ID DESC");
		
		sql = sqlBulider.toString();
		
		result = (PageResult<Map<String, Object>>)this.pageQuery(sql,
																paramList,
																this.getClass().getName()+".queryClaim()",
																pageSize,
																curPage);
		return result;
	}
	
	/**
	 * 拼查询条件，如果页面查询过来不为空，则拼装到查询条件中
	 * @param param 参数列 对应数据库中字段
	 * @param value 参数值
	 * @param oper 操作符
	 * @param sBuilder 拼装条件容器
	 * @param paramList 参数列表
	 * @param dataType 数据类型
	 *        1 : 时间
	 *        2 ：其他
	 * @param dataFormat 数据格式，现在只有时间类型需要添加格式
	 * @param table 标明表名或别名
	 * @return
	 */
	private void createWhereMap(String param,String oper,String value,
			StringBuilder sBuilder,List<Object> paramList,int dataType,
			String dataFormat,String table){
		if(Utility.testString(value)){
			param = table + "." + param;
			if(dataType==1) {//时间
				sBuilder.append(" AND ").append(param).append(" ")
				.append(oper).append(" TO_DATE(" +"?" + ",'" + dataFormat + "')");
				paramList.add(value);
			}else if("IN".equalsIgnoreCase(oper)){
				StringTokenizer st = new StringTokenizer(value,",");
				
				boolean flag = true;
				while(st.hasMoreTokens()){
					String tempValue = st.nextToken();
					if(Utility.testString(tempValue)){
						if(flag)//保证只加一次AND COL IN (
							sBuilder.append(" AND ").append(param).append(" ").append(oper).append(" (");
						flag = false;
						sBuilder.append("?,");
						paramList.add(tempValue);
					}
				}
				if(!flag)
					sBuilder.append("'')");//加入后半个括号，同时多加一个空''
			}else{//其他
				sBuilder.append(" AND ").append(param).append(" ").append(oper).append(" ?");
				if("LIKE".equalsIgnoreCase(oper)){//模糊查询
					paramList.add("%" +value +"%");
				} else{
					paramList.add(value);
				}
			}
		}
		//sBuilder.append("\n");
	}
	
	/**
	 * 根据用户ID查询用户信息
	 * @param userId 用户ID
	 * @return TcUserPO
	 */
	public TcUserPO queryUserById(Long userId){
		TcUserPO conditionPO = new TcUserPO();
		conditionPO.setUserId(userId);
		List<PO> userList = this.select(conditionPO);
		
		TcUserPO resultPO = null;
		if(userList!=null && userList.size()>0){
			resultPO = (TcUserPO) userList.get(0);
		}
		return resultPO;
	}
	
	/**
	 * 更新索赔之配件信息
	 * @param partPO 需要更新的信息
	 * @param partId 索赔之配件ID
	 * @return
	 */
	public int updatePartsInfo(TtAsWrPartsitemPO partPO,Long partId){
		
		TtAsWrPartsitemPO conditionPO = new TtAsWrPartsitemPO();
		conditionPO.setPartId(partId);
		
		return this.update(conditionPO, partPO);
	}
	
	/**
	 * 更新索赔之工时信息
	 * @param labourPO 需要更新的信息
	 * @param labourId 索赔之工时ID
	 * @return
	 */
	public int updateLabourInfo(TtAsWrLabouritemPO labourPO,Long labourId){
		
		TtAsWrLabouritemPO conditionPO = new TtAsWrLabouritemPO();
		conditionPO.setLabourId(labourId);
		
		return this.update(conditionPO, labourPO);
	}
	
	/**
	 * 更新索赔之其他项目信息
	 * @param otherPO 需要更新的信息
	 * @param otherId 索赔之其他项目ID
	 * @return
	 */
	public int updateOtherInfo(TtAsWrNetitemPO otherPO,Long otherId){
		
		TtAsWrNetitemPO conditionPO = new TtAsWrNetitemPO();
		conditionPO.setNetitemId(otherId);
		
		return this.update(conditionPO, otherPO);
	}
	
	/**
	 * 更新索赔申请单信息
	 * @param claimPO 需要更新的信息
	 * @param claimId 索赔申请单ID
	 * @return
	 */
	public int updateClaimInfo(TtAsWrApplicationPO claimPO,Long claimId){
		
		TtAsWrApplicationPO conditionPO = new TtAsWrApplicationPO();
		conditionPO.setId(claimId);
		
		return this.update(conditionPO, claimPO);
	}
	
	public PageResult<Map<String, Object>> queryAccAuditList(auditBean bean,int curPage, int pageSize){
		StringBuffer sql= new StringBuffer();
		sql.append("SELECT ROWNUM NUM,r.region_name, a.ID, a.BALANCE_NO, a.DEALER_CODE, a.DEALER_NAME, a.CLAIM_COUNT,a.RETURN_AMOUNT,\n" );
		sql.append("       TO_CHAR(a.CREATE_DATE, 'YYYY-MM-DD') CREATE_DATE, a.STATUS, a.YIELDLY,a.BALANCE_AMOUNT,a.AMOUNT_SUM,TO_CHAR(t.BEGIN_BALANCE_DATE,'YYYY-MM-DD') AS BEGIN_BALANCE_DATE,TO_CHAR(t.END_BALANCE_DATE,'YYYY-MM-DD') AS END_BALANCE_DATE,\n" );
		sql.append("TO_CHAR(a.START_DATE,'YYYY-MM-DD') AS START_DATE,TO_CHAR(a.END_DATE,'YYYY-MM-DD') AS END_DATE,\n");
		/*********add by liuxh 20101126 增加索赔单已审核数量，特殊费用单数量和已审核数量************/
//		sql.append("(SELECT COUNT (1) FROM tt_as_wr_application b, tr_balance_claim c WHERE b.ID = c.claim_id and c.balance_id=a.id) as COUNT_APPL, \n ");
//		sql.append("(SELECT COUNT (1) FROM tt_as_wr_application b, tr_balance_claim c WHERE b.ID = c.claim_id\n ");
//		sql.append(" AND c.balance_id = a.ID AND b.status = "+Constant.CLAIM_APPLY_ORD_TYPE_07+") AS claim_count_al,\n");
		sql.append("(SELECT COUNT(1) FROM TT_AS_WR_SPEFEE D WHERE D.CLAIMBALANCE_ID=A.ID) AS SP_COUNT,\n");
		sql.append("(SELECT COUNT(1) FROM TT_AS_WR_SPEFEE D WHERE D.CLAIMBALANCE_ID=A.ID AND D.STATUS IN("+Constant.SPEFEE_STATUS_06+","+Constant.SPEFEE_STATUS_05+")) AS SP_COUNT_AL\n");
		/*********add by liuxh 20101126 增加索赔单已审核数量，特殊费用单数量和已审核数量************/
		sql.append(" FROM TT_AS_WR_CLAIM_BALANCE a ,tm_dealer t,tm_region r\n");
		sql.append("WHERE a.OEM_COMPANY_ID = ").append(bean.getOemCompanyId()).append("\n");
		sql.append(" AND a.YIELDLY IN (").append(bean.getYieldlys()).append(") and a.dealer_id=t.dealer_id\n");
		sql.append(" and t.province_id = r.region_code\n");
		if(Utility.testString(bean.getDealerCode())){
			//用于对多个查询多个经销商，不可模糊sql.append("AND a.DEALER_CODE IN (").append(bean.getDealerCode()).append(")\n");
			sql.append("AND a.DEALER_CODE LIKE '%").append(bean.getDealerCode()).append("%'\n");
		}
		
		if(Utility.testString(bean.getStartDate())){
			sql.append("AND a.CREATE_DATE >= TO_DATE('").append(bean.getStartDate()).append("', 'YYYY-MM-DD HH24:MI:SS')\n");
		}
		if(Utility.testString(bean.getEndDate())){
			sql.append("AND a.CREATE_DATE <= TO_DATE('").append(bean.getEndDate()).append("', 'YYYY-MM-DD HH24:MI:SS')\n");
		}
		if(Utility.testString(bean.getYieldly())){
			sql.append("AND a.YIELDLY = ").append(bean.getYieldly()).append("\n");
		}
		if(Utility.testString(bean.getStatus())){
			sql.append("AND a.STATUS = ").append(bean.getStatus()).append("\n");
		}else{
			sql.append("and a.status not in ("+Constant.ACC_STATUS_07+","+Constant.ACC_STATUS_08+","+Constant.ACC_STATUS_09+")\n");
		}
		if(Utility.testString(bean.getBalanceNo())){
			sql.append("AND a.BALANCE_NO LIKE '%").append(bean.getBalanceNo()).append("%'\n");
		}

		sql.append("ORDER BY a.ID DESC");
		//System.out.println("sqlsql=="+sql);
		PageResult<Map<String, Object>> ps = pageQuery(sql.toString(), null, getFunName(), pageSize, curPage);
		if(ps.getTotalRecords()>0){
			List list=ps.getRecords();
			String sql2="SELECT /*+RULE*/ COUNT (1) as count FROM tt_as_wr_application b, tr_balance_claim c WHERE b.ID = c.claim_id and c.balance_id=?";
			String sql3="SELECT /*+RULE*/ COUNT (1) as count FROM tt_as_wr_application b, tr_balance_claim c WHERE b.ID = c.claim_id AND b.status = "+Constant.CLAIM_APPLY_ORD_TYPE_07+" AND c.balance_id = ? ";
			for(int i=0;i<list.size();i++)
			{
				Map map=(Map)list.get(i);
				List listPar=new ArrayList();
				listPar.add(((BigDecimal)map.get("ID")).longValue());
				Map map2=this.pageQueryMap(sql2, listPar, this.getFunName());
				Map map3=this.pageQueryMap(sql3, listPar, this.getFunName());
				map.put("COUNT_APPL",((BigDecimal)map2.get("COUNT")).longValue());
				map.put("CLAIM_COUNT_AL",((BigDecimal)map3.get("COUNT")).longValue());
			}
		}
		return ps;
	}

	public PageResult<Map<String, Object>> queryAccAuditById(auditBean bean,int curPage, int pageSize){
		StringBuffer sql= new StringBuffer();
		sql.append("SELECT ROWNUM NUM, A.ID CLAIM_ID, A.CLAIM_NO, A.YIELDLY,\n" );
		sql.append("       TO_CHAR(A.REPORT_DATE, 'YYYY-MM-DD') REPORT_DATE,\n" );
		sql.append("       TO_CHAR(A.AUDITING_DATE, 'YYYY-MM-DD') AUDITING_DATE,\n" );
		sql.append("       A.SERIES_NAME, A.MODEL_NAME, A.VIN, C.BALANCE_ID,\n" );
		//sql.append("     B.DEALER_SHORTNAME DEALER_NAME,A.CLAIM_TYPE,A.STATUS,A.AUTH_CODE,A.BALANCE_AMOUNT,A.REPAIR_TOTAL,\n" );
		/******MOD BY LIUXH 20101203 索赔单申请费用加上追加工时******/
		sql.append("     B.DEALER_SHORTNAME DEALER_NAME,A.CLAIM_TYPE,A.STATUS,A.AUTH_CODE,A.BALANCE_AMOUNT,(NVL(A.REPAIR_TOTAL,0)) AS REPAIR_TOTAL,\n" );
		/******MOD BY LIUXH 20101203 索赔单申请费用加上追加工时******/
		sql.append("     (CASE WHEN A.STATUS = "+Constant.CLAIM_APPLY_ORD_TYPE_08+" AND A.AUTH_CODE = '"+CommonUtils.checkNull(bean.getAuthCodeOrder())+"' THEN 1 ELSE 2 END) ORDERSTR\n" );
		sql.append("FROM TT_AS_WR_APPLICATION A, TM_DEALER B, TR_BALANCE_CLAIM C\n" );
		sql.append("WHERE A.DEALER_ID = B.DEALER_ID\n" );
		sql.append("AND A.ID = C.CLAIM_ID\n" );
		
		List<Object> params = new ArrayList<Object>();
		if(Utility.testString(bean.getDealerCode())){
			sql.append(Utility.getConSqlByParamForEqual(bean.getDealerCode(), params, "B", "DEALER_CODE"));
		}
		if(Utility.testString(bean.getVin())){
			sql.append("AND A.VIN LIKE '%").append(bean.getVin()).append("%'\n");
		}
		if(Utility.testString(bean.getClaimNo())){
			sql.append("AND A.CLAIM_NO LIKE UPPER('%").append(bean.getClaimNo()).append("%')\n");
		}
		if(Utility.testString(bean.getModelCode())){
			sql.append("AND A.MODEL_CODE LIKE '%").append(bean.getModelCode()).append("%'\n");
		}
		if(Utility.testString(bean.getClaimStatus())){
			sql.append("AND A.STATUS = '").append(bean.getClaimStatus()).append("'\n");
		}
		if(Utility.testString(bean.getAuthCode())){
			sql.append("AND A.AUTH_CODE = '").append(bean.getAuthCode()).append("'\n");
		}
		if(Utility.testString(bean.getClaimType())){
			sql.append("AND A.CLAIM_TYPE = '").append(bean.getClaimType()).append("'\n");
		}
		if(Utility.testString(bean.getStartDate())){
			sql.append("AND A.CREATE_DATE >= TO_DATE('").append(bean.getStartDate()).append("', 'YYYY-MM-DD HH24:MI:SS')\n");
		}
		if(Utility.testString(bean.getEndDate())){
			sql.append("AND A.CREATE_DATE <= TO_DATE('").append(bean.getEndDate()).append("', 'YYYY-MM-DD HH24:MI:SS')\n");
		}
		if(!CommonUtils.checkNull(bean.getCantNotAudit()).equals("")){
            sql.append("AND A.ID NOT IN("+bean.getCantNotAudit()+") ");
        }
		sql.append("AND C.BALANCE_ID = ").append(bean.getId()).append("\n");
		sql.append("ORDER BY A.CLAIM_NO,A.CLAIM_TYPE , a.AUDITING_DATE ASC");

		PageResult<Map<String, Object>> ps = pageQuery(sql.toString(), params, getFunName(), pageSize, curPage);
		return ps;
	}
	public PageResult<Map<String, Object>> queryAccAuditSkipById(auditBean bean,int curPage, int pageSize){
		StringBuffer sql= new StringBuffer();
		sql.append("SELECT ROWNUM NUM, A.ID CLAIM_ID, A.CLAIM_NO, A.YIELDLY,\n" );
		sql.append("       TO_CHAR(A.REPORT_DATE, 'YYYY-MM-DD') REPORT_DATE,\n" );
		sql.append("       TO_CHAR(A.AUDITING_DATE, 'YYYY-MM-DD') AUDITING_DATE,\n" );
		sql.append("       A.SERIES_NAME, A.MODEL_NAME, A.VIN, C.BALANCE_ID,\n" );
		//sql.append("     B.DEALER_SHORTNAME DEALER_NAME,A.CLAIM_TYPE,A.STATUS,A.AUTH_CODE,A.BALANCE_AMOUNT,A.REPAIR_TOTAL,\n" );
		/******MOD BY LIUXH 20101203 索赔单申请费用加上追加工时******/
		sql.append("     B.DEALER_SHORTNAME DEALER_NAME,A.CLAIM_TYPE,A.STATUS,A.AUTH_CODE,A.BALANCE_AMOUNT,(NVL(A.REPAIR_TOTAL,0)+NVL(A.APPENDLABOUR_AMOUNT,0)) AS REPAIR_TOTAL,\n" );
		/******MOD BY LIUXH 20101203 索赔单申请费用加上追加工时******/
		sql.append("     (CASE WHEN A.STATUS = "+Constant.CLAIM_APPLY_ORD_TYPE_08+" AND A.AUTH_CODE = '"+CommonUtils.checkNull(bean.getAuthCodeOrder())+"' THEN 1 ELSE 2 END) ORDERSTR\n" );
		sql.append("FROM TT_AS_WR_APPLICATION A, TM_DEALER B, TR_BALANCE_CLAIM C\n" );
		sql.append("WHERE A.DEALER_ID = B.DEALER_ID\n" );
		sql.append("AND A.ID = C.CLAIM_ID\n" );
		
		List<Object> params = new ArrayList<Object>();
		if(Utility.testString(bean.getDealerCode())){
			sql.append(Utility.getConSqlByParamForEqual(bean.getDealerCode(), params, "B", "DEALER_CODE"));
		}
		if(Utility.testString(bean.getVin())){
			sql.append("AND A.VIN LIKE '%").append(bean.getVin()).append("%'\n");
		}
		if(Utility.testString(bean.getClaimNo())){
			sql.append("AND A.CLAIM_NO LIKE UPPER('%").append(bean.getClaimNo()).append("%')\n");
		}
		if(Utility.testString(bean.getModelCode())){
			sql.append("AND A.MODEL_CODE LIKE '%").append(bean.getModelCode()).append("%'\n");
		}
		if(Utility.testString(bean.getClaimStatus())){
			sql.append("AND A.STATUS = '").append(bean.getClaimStatus()).append("'\n");
		}
		if(Utility.testString(bean.getAuthCode())){
			sql.append("AND A.AUTH_CODE = '").append(bean.getAuthCode()).append("'\n");
		}
		if(Utility.testString(bean.getClaimType())){
			sql.append("AND A.CLAIM_TYPE = '").append(bean.getClaimType()).append("'\n");
		}
		if(Utility.testString(bean.getStartDate())){
			sql.append("AND A.CREATE_DATE >= TO_DATE('").append(bean.getStartDate()).append("', 'YYYY-MM-DD HH24:MI:SS')\n");
		}
		if(Utility.testString(bean.getEndDate())){
			sql.append("AND A.CREATE_DATE <= TO_DATE('").append(bean.getEndDate()).append("', 'YYYY-MM-DD HH24:MI:SS')\n");
		}
		if(!bean.getCantNotAudit().equals("")){
			sql.append("AND A.ID NOT IN("+bean.getCantNotAudit()+")");
		}
		sql.append("AND C.BALANCE_ID = ").append(bean.getId()).append("\n");
		sql.append("ORDER BY a.AUDITING_DATE ASC");

		PageResult<Map<String, Object>> ps = pageQuery(sql.toString(), params, getFunName(), pageSize, curPage);
		return ps;
	}
	
	public String getClaimIds(String id){
		String climeId = "";
		StringBuffer sql= new StringBuffer();
		sql.append("SELECT CLAIM_ID\n" );
		sql.append("FROM TR_BALANCE_CLAIM\n" );
		sql.append("WHERE BALANCE_ID = ").append(id).append("\n");
		List<Map<String, Object>> list = pageQuery(sql.toString(), null, getFunName());
		if(list!=null&&list.size()>0){
			for(int i = 0 ; i < list.size(); i++){
				if(!climeId.equals("")){
					climeId = "," + list.get(i).get("CLAIM_ID");
				}else{
					climeId = String.valueOf(list.get(i).get("CLAIM_ID"));
				}
			}
		}
		return climeId;
	}
	
	public void updateClaimStatus(String cliamId){
		StringBuffer sql= new StringBuffer();
		sql.append("UPDATE TT_AS_WR_APPLICATION\n" );
		sql.append("SET STATUS = ").append(Constant.CLAIM_APPLY_ORD_TYPE_07).append("\n"); 
		sql.append("WHERE ID IN (").append(cliamId).append(")\n");

		update(sql.toString(), null);
	}
	
	/**
	 * 批量审核通过，将对应结算单中的索赔单批量通过
	 * 同时标识对应结算单审核通过
	 * @param balanceId 结算单ID
	 */
	@Deprecated
	public void modifyClaimStatus(Long balanceId){
		//更新索赔单状态
		StringBuilder sql= new StringBuilder();
		sql.append("UPDATE TT_AS_WR_APPLICATION A\n" );
		sql.append("   SET A.STATUS = ?\n" );
		sql.append(" WHERE 1 = 1\n" );
		sql.append("   AND EXISTS (SELECT B.ID\n" );
		sql.append("          FROM TR_BALANCE_CLAIM B\n" );
		sql.append("         WHERE B.CLAIM_ID = A.ID\n" );
		sql.append("           AND B.BALANCE_ID = ?)");

		List<Object> paramList = new ArrayList<Object>();
		paramList.add(Constant.CLAIM_APPLY_ORD_TYPE_07);
		paramList.add(balanceId);
		
		this.update(sql.toString(), paramList);
		
		//更新结算单同索赔单中关系状态
		StringBuilder sql2= new StringBuilder();
		sql2.append("UPDATE TR_BALANCE_CLAIM\n" );
		sql2.append("   SET STATUS = ?\n" );
		sql2.append(" WHERE 1 = 1\n" );
		sql2.append("   AND BALANCE_ID = ?");
		
		List<Object> paramList2 = new ArrayList<Object>();
		paramList2.add(Constant.STATUS_ENABLE);
		paramList2.add(balanceId);

		this.update(sql2.toString(), paramList2);
	}
	
	/**
	 * 查询结算单信息
	 * @param balanceId 结算单ID
	 * @return
	 */
	public TtAsWrClaimBalancePO queryBalanceOrderById(Long balanceId){
		TtAsWrClaimBalancePO resultPO = new TtAsWrClaimBalancePO();
		TtAsWrClaimBalancePO conditionPO = new TtAsWrClaimBalancePO();
		conditionPO.setId(balanceId);
		List<PO> resultList = this.select(conditionPO);
		if(resultList!=null && resultList.size()>0)
			resultPO = (TtAsWrClaimBalancePO)resultList.get(0);
		return resultPO;
	}
	
	/**
	 * 更新索赔单中工时、配件和其他项目的结算金额
	 * 注：只有对应工时和配件、其他项目单项审核同意才能修改
	 * @param claimId 索赔单ID
	 * @param isCheckAgreeStatus 是否判断 授权审核 状态（IS_AGREE） true :使用，false 不使用
	 */
	public void modifyClaimBalanceDetailAmount(Long claimId,boolean isCheckAgreeStatus){
		
		StringBuilder sql= new StringBuilder();
		sql.append("UPDATE TT_AS_WR_APPLICATION A\n" );
		sql.append("   SET A.BALANCE_LABOUR_AMOUNT  = (SELECT NVL(SUM(B.BALANCE_AMOUNT), 0)\n" );
		sql.append("                                     FROM TT_AS_WR_LABOURITEM B\n" );
		sql.append("                                    WHERE B.ID = A.ID\n" );
		if(isCheckAgreeStatus)
			sql.append("                                    AND B.IS_AGREE = "+Constant.IF_TYPE_YES +"\n");
		sql.append("),\n");
		sql.append("       A.BALANCE_PART_AMOUNT    = (SELECT NVL(SUM(B.BALANCE_AMOUNT), 0)\n" );
		sql.append("                                     FROM TT_AS_WR_PARTSITEM B\n" );
		sql.append("                                    WHERE B.ID = A.ID\n" );
		if(isCheckAgreeStatus)
			sql.append("                                    AND B.IS_AGREE = "+Constant.IF_TYPE_YES +"\n");
		sql.append("),\n");
		sql.append("       A.BALANCE_NETITEM_AMOUNT = (SELECT NVL(SUM(B.BALANCE_AMOUNT), 0) FROM TT_AS_WR_NETITEM B\n" );
		sql.append("                                    WHERE B.ID = A.ID\n" );
		if(isCheckAgreeStatus)
			sql.append("                                    AND B.IS_AGREE = "+Constant.IF_TYPE_YES +"\n");
		sql.append(")\n");
		sql.append("WHERE 1=1\n" );
		sql.append("AND A.ID = ?");
		
		List<Object> params = new ArrayList<Object>();
		params.add(claimId);
		
		this.update(sql.toString(), params);
	}
	
	/**
	 * 更新索赔单的结算金额
	 * @param claimId 索赔单ID
	 * @param isCheckAgreeStatus 是否判断 授权审核 状态（IS_AGREE） true :使用，false 不使用
	 */
	public void modifyClaimBalanceAmount(Long claimId,boolean isCheckAgreeStatus){
		
		List<Object> params = new ArrayList<Object>();
		params.add(claimId);
		
		StringBuilder sql1= new StringBuilder();
		//更新索赔单中配件、工时和其他项目的结算金额和数量
		sql1.append("UPDATE TT_AS_WR_APPLICATION A\n" );
		sql1.append("   SET (A.BALANCE_LABOUR_AMOUNT, A.LABOUR_HOURS) = (SELECT NVL(SUM(B.BALANCE_AMOUNT),0),NVL(SUM(B.BALANCE_QUANTITY),0)\n" );
		sql1.append("                                                                             FROM TT_AS_WR_LABOURITEM B\n" );
		sql1.append("                                    WHERE B.ID = A.ID\n" );
		if(isCheckAgreeStatus)
			sql1.append("                                    AND B.IS_AGREE = "+Constant.IF_TYPE_YES +"\n");
		sql1.append("),\n");
		sql1.append("       (A.BALANCE_PART_AMOUNT, A.PARTS_COUNT) = (SELECT NVL(SUM(C.BALANCE_AMOUNT),0),NVL(SUM(C.BALANCE_QUANTITY),0)\n" );
		sql1.append("                                                                          FROM TT_AS_WR_PARTSITEM C\n" );
		sql1.append("                                    WHERE C.ID = A.ID\n" );
		if(isCheckAgreeStatus)
			sql1.append("                                    AND C.IS_AGREE = "+Constant.IF_TYPE_YES +"\n");
		sql1.append("),\n");sql1.append("       A.BALANCE_NETITEM_AMOUNT = (SELECT NVL(SUM(D.BALANCE_AMOUNT), 0)\n" );
		sql1.append("                                     FROM TT_AS_WR_NETITEM D\n" );
		sql1.append("                                    WHERE D.ID = A.ID\n" );
		if(isCheckAgreeStatus)
			sql1.append("                                    AND D.IS_AGREE = "+Constant.IF_TYPE_YES +"\n");
		sql1.append(")\n");
		sql1.append(" WHERE A.ID = ?");
		this.update(sql1.toString(), params);
		
		StringBuilder sql= new StringBuilder();
		sql.append("UPDATE TT_AS_WR_APPLICATION A\n" );
		sql.append("   SET A.BALANCE_AMOUNT = (NVL(A.BALANCE_LABOUR_AMOUNT, 0) +\n" );
		sql.append("                          NVL(A.BALANCE_PART_AMOUNT, 0) +\n" );
		sql.append("                          NVL(A.BALANCE_NETITEM_AMOUNT, 0) +\n" );
		sql.append("                          NVL(A.FREE_M_PRICE, 0) +\n" );
		sql.append("                          NVL(A.CAMPAIGN_FEE, 0) +\n" );
		sql.append("                          NVL(A.APPENDLABOUR_AMOUNT, 0))\n" );
		sql.append(" WHERE A.ID = ?");
        
		this.update(sql.toString(), params);
	}
	
	
	public void modifyClaimBalanceAmount2(Long claimId,boolean isCheckAgreeStatus){
		List<Object> params = new ArrayList<Object>();
		params.add(claimId);
		StringBuilder sql2= new StringBuilder();
		sql2.append("update TT_AS_WR_APPLICATION wa \n");
		sql2.append("   set wa.labour_amount  = wa.balance_labour_amount,\n" );
		sql2.append("       wa.part_amount    = wa.balance_part_amount,\n" );
		sql2.append("       wa.netitem_amount = wa.balance_netitem_amount,\n" );
		sql2.append("       wa.repair_total = wa.balance_amount,\n" );
		sql2.append("wa.std_labour_amount = wa.balance_labour_amount \n");
		sql2.append("       where wa.id=?\n" );
		sql2.append("\n" );
		sql2.append("");
		this.update(sql2.toString(), params);
		
	}
	/**
	 * 根据索赔单Id查询索赔单明细
	 * @param claimId 索赔单ID
	 * @return
	 */
	public TtAsWrApplicationExtPO queryClaimOrderDetailById(String claimId){
		StringBuilder sql= new StringBuilder();
		sql.append("SELECT A.*,\n" );
		sql.append("       B.DEALER_CODE,\n" );
		sql.append("       B.DEALER_NAME,\n" );
		sql.append("       E.CODE_DESC CLAIM_NAME,\n" );
		sql.append("       D.GROUP_CODE PACKAGE_NAME,C.PURCHASED_DATE PURCHASED_DATE,\n" );
		sql.append("       c.product_date,b.is_dqv,b.phone");
		sql.append("  FROM TT_AS_WR_APPLICATION   A,\n" );
		sql.append("       TM_DEALER              B,\n" );
		sql.append("       TM_VEHICLE             C,\n" );
		sql.append("       TM_VHCL_MATERIAL_GROUP D,\n" );
		sql.append("       TC_CODE                E\n" );
		sql.append(" WHERE 1 = 1\n" );
		sql.append("   AND A.DEALER_ID = B.DEALER_ID(+)\n" );
		sql.append("   AND A.VIN = C.VIN(+)\n" );
		sql.append("   AND C.PACKAGE_ID = D.GROUP_ID(+)\n" );
		sql.append("   AND A.CLAIM_TYPE = E.CODE_ID(+)\n" );
		sql.append("   AND A.ID = ?");

		List<Object> params = new ArrayList<Object>();
		params.add(CommonUtils.checkNull(claimId));
		
		List<TtAsWrApplicationExtPO> claimList = this.select(TtAsWrApplicationExtPO.class,sql.toString(), params);
		TtAsWrApplicationExtPO resultPO = new TtAsWrApplicationExtPO();
		
		if(claimList!=null && claimList.size()>0)
			resultPO = claimList.get(0);
		
		return resultPO;
	}
	
	//zhumingwei 2011-03-04
	public List<Map<String, Object>> getAuthInfo(String id){
		StringBuffer sql= new StringBuffer();
		sql.append("select a.approval_person\n" );
		sql.append("from tt_as_wr_appauthitem a\n" );
		sql.append("where a.approval_date =\n" );
		sql.append("(select max(b.approval_date) from tt_as_wr_appauthitem b where b.approval_result="+Constant.CLAIM_APPLY_ORD_TYPE_04+" and a.id=b.id) and a.id="+id+"\n" );
		List<Map<String, Object>> list = pageQuery(sql.toString(), null, getFunName());
		return list;
	}
	
	//zhumingwei 2011-03-04
	public String countRepairTimes(String id){
		StringBuffer sql= new StringBuffer();
		sql.append("select count(*) as repair_times from tt_as_repair_order www where www.vin = (select aaa.vin from tt_as_wr_application aaa where aaa.id = '"+id+"')\n" );
		List<Map<String, Object>> list = pageQuery(sql.toString(), null, getFunName());
		String count = ((Map)list.get(0)).get("REPAIR_TIMES").toString();
		return count;
	}
	
	//查询服务活动代码与名称
	public List getActivity(String id){
		StringBuffer sql = new StringBuffer("\n");
		sql.append("select a.activity_code,a.activity_name\n");
		sql.append("  from tt_as_activity a, tt_as_wr_application b, tt_as_repair_order c\n");  
		sql.append(" where b.ro_no = c.ro_no\n");  
		sql.append("   and c.cam_code = a.activity_code\n");  
		sql.append("   and b.id ="+id);
		return  this.select(TtAsActivityPO.class,sql.toString(),null);
	}
	
	/**
	 * Iverson 2010-11-25
	 * @param claimId 索赔单ID
	 * @return
	 */
	public TtAsWrApplicationExtPO queryClaimOrderDetailById11(String claimId){
		StringBuilder sql= new StringBuilder();
		sql.append("SELECT A.*,\n" );
		sql.append("       B.DEALER_CODE,\n" );
		sql.append("       B.DEALER_NAME,\n" );
		sql.append("       E.CODE_DESC CLAIM_NAME,\n" );
		sql.append("       D.GROUP_NAME PACKAGE_NAME,C.PURCHASED_DATE PURCHASED_DATE\n" );
		sql.append("  FROM tt_as_wr_application_backup   A,\n" );
		sql.append("       TM_DEALER              B,\n" );
		sql.append("       TM_VEHICLE             C,\n" );
		sql.append("       TM_VHCL_MATERIAL_GROUP D,\n" );
		sql.append("       TC_CODE                E\n" );
		sql.append(" WHERE 1 = 1\n" );
		sql.append("   AND A.DEALER_ID = B.DEALER_ID(+)\n" );
		sql.append("   AND A.VIN = C.VIN(+)\n" );
		sql.append("   AND C.PACKAGE_ID = D.GROUP_ID(+)\n" );
		sql.append("   AND A.CLAIM_TYPE = E.CODE_ID(+)\n" );
		sql.append("   AND A.ID = ?");

		List<Object> params = new ArrayList<Object>();
		params.add(CommonUtils.checkNull(claimId));
		
		List<TtAsWrApplicationExtPO> claimList = this.select(TtAsWrApplicationExtPO.class,sql.toString(), params);
		TtAsWrApplicationExtPO resultPO = new TtAsWrApplicationExtPO();
		
		if(claimList!=null && claimList.size()>0)
			resultPO = claimList.get(0);
		
		return resultPO;
	}
	
	/**
	 * 当索赔单类型为"审核退回"将子表金额和状态更新到是和申请时金额
	 */
	public void modifyBackClaimDetail(Long claimId){
		StringBuilder sql= new StringBuilder();
		//将配件信息更新回去
		sql.append("UPDATE TT_AS_WR_PARTSITEM A\n" );
		sql.append("SET A.BALANCE_AMOUNT = A.AMOUNT,\n" );
		sql.append("A.BALANCE_QUANTITY = A.QUANTITY,\n" );
		sql.append("A.IS_AGREE = "+Constant.IF_TYPE_YES+"\n");
		sql.append("WHERE 1=1\n" );
		sql.append("AND EXISTS (SELECT B.ID FROM TT_AS_WR_APPLICATION B WHERE B.ID = A.ID\n" );
		sql.append("AND B.STATUS = "+Constant.CLAIM_APPLY_ORD_TYPE_06+")\n" );
		sql.append("AND A.ID = "+claimId);
		//将工时信息更新回去
		StringBuilder sql1= new StringBuilder();
		sql1.append("UPDATE TT_AS_WR_LABOURITEM A\n" );
		sql1.append("SET A.BALANCE_AMOUNT = A.LABOUR_AMOUNT,\n" );
		sql1.append("A.BALANCE_QUANTITY = A.LABOUR_HOURS,\n" );
		sql1.append("A.IS_AGREE = "+Constant.IF_TYPE_YES+"\n");
		sql1.append("WHERE 1=1\n" );
		sql1.append("AND EXISTS (SELECT B.ID FROM TT_AS_WR_APPLICATION B WHERE B.ID = A.ID\n" );
		sql1.append("AND B.STATUS = "+Constant.CLAIM_APPLY_ORD_TYPE_06+")\n" );
		sql1.append("AND A.ID = "+claimId);
		//将其他项目信息更新回去
		StringBuilder sql2= new StringBuilder();
		sql2.append("UPDATE TT_AS_WR_NETITEM A\n" );
		sql2.append("SET A.BALANCE_AMOUNT = A.AMOUNT,\n" );
		sql2.append("A.IS_AGREE = "+Constant.IF_TYPE_YES+"\n");
		sql2.append("WHERE 1=1\n" );
		sql2.append("AND EXISTS (SELECT B.ID FROM TT_AS_WR_APPLICATION B WHERE B.ID = A.ID\n" );
		sql2.append("AND B.STATUS = "+Constant.CLAIM_APPLY_ORD_TYPE_06+")\n" );
		sql2.append("AND A.ID = "+claimId);

		this.update(sql.toString(), null);
		this.update(sql1.toString(), null);
		this.update(sql2.toString(), null);
	}
	
	/**
	 * Iverson add with 2010-12-16
	 * 废弃索赔单是首先查询此索赔单已经被生成结算单没有
	 * @Title: isCount
	 */
	public int isCount(String id){
		int count = 0;
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(*) COUNT FROM tr_balance_claim WHERE claim_id ='"+id+"' ");
		Map<String, Object> map = pageQueryMap(sql.toString(), null, getFunName());
		count = Integer.parseInt(String.valueOf(map.get("COUNT")));
		return count;
	}
}
